<?php
/**
 * Handles and manages an company and its entity in database
 * - based on CRUD
 *
 */
class NewsModel extends Model {

    public function getNumCat()
    {
        return $this->db->query("SELECT `number`, `cat` FROM `klimat_prod_cat`");

    }

    public function getGroupID($id)
    {      
        return $this->db->query("SELECT `group_id` as `no` FROM `news_groups` WHERE `news_id`=?", 'i', $id);
    }

     public function getAdd($categories)
    {
        return $this->db->query("SELECT kpl.`id`, kpl.`link`, kpl.`company`, kpl.`name`, kpl.`subtext`, kpl.`text`, kpl.`Logo` FROM `klimat_prod_list` AS `kpl`
							JOIN `klimat_prod_list_groups` AS `kplg` ON (kpl.id=kplg.kpl_id)
							WHERE
							kplg.`kpc_id` in (".$categories.") AND kpl.`approved` = '1' AND kpl.`LogoSize`>='100' AND kpl.`newProduct`='0' AND
							(CASE kpl.`LogoActive` WHEN 1 THEN (CASE WHEN kpl.`LogoStart`<NOW()
							AND (kpl.`LogoEnd`='0000-00-00 00:00:00' OR kpl.`LogoEnd` > NOW()) THEN 1 ELSE 0 END) ELSE 0 END)=1
							ORDER BY kpl.`LogoSize` DESC, kpl.`company` ASC ");// remove duplicat entries of products... ie. not display 2 or more of the same add in different groups
    }
    public function getAddLH($catLow, $catHigh)
    {
        return $this->db->query("SELECT kpl.`id`, kpl.`link`, kpl.`company`, kpl.`name`, kpl.`subtext`, kpl.`text`, kpl.`Logo` FROM `klimat_prod_list` AS `kpl`
							JOIN `klimat_prod_list_groups` AS `kplg` ON (kpl.id=kplg.kpl_id)
							WHERE
							kplg.`kpc_id` BETWEEN $catLow AND $catHigh AND kpl.`approved` = '1' AND kpl.`LogoSize`>='100' AND kpl.`newProduct`='0' AND
							(CASE kpl.`LogoActive` WHEN 1 THEN (CASE WHEN kpl.`LogoStart`<NOW()
							AND (kpl.`LogoEnd`='0000-00-00 00:00:00' OR kpl.`LogoEnd` > NOW()) THEN 1 ELSE 0 END) ELSE 0 END)=1
							ORDER BY kpl.`LogoSize` DESC, kpl.`company` ASC ");// remove duplicat entries of products... ie. not display 2 or more of the same add in different groups
    }

    //Funkar
    //Tar ut nyheter med $id som har ett dagens datum mellan date_publish och date_end. Approved = 1 och deleted = 0
    public function getNews($id)
    {

        return $this->db->query("SELECT `id`, `title`, `text`, `text2`, `mainlink`,  `mainlink_activated`,
                                                                `date_publish`, `date_publish2`, `date_publish3`, `date_publish4`, `user_id`,
                                                                `text_source`, `text_source_activated`, `img_file`, `img_source`, `img_description`,
                                                                `img_url_activated`, `img_url`
                                                                FROM `news` WHERE `id`=? AND `approved`='1' AND `deleted`='0'
                                                                AND NOW() BETWEEN `date_publish` AND `date_end` LIMIT 1", "i", $id);
    }

    //Funkar
    //ökar Views värdet i News för antalet visningar av nyheten
    public function IncreaseViews($id)
    {
       return $this->db->query("UPDATE `news` SET `views` = `views` + 1 WHERE `id`=? LIMIT 1", "i", $id);
    }

    public function getCat($category, $sub)
    {
        return $this->db->query("SELECT `cat` FROM `klimat_prod_cat` WHERE `number`='$category"."$sub' AND `hidden`=0 LIMIT 1");
    }

    public function getCatNewsID($category, $sub)
    {
        return $this->db->query("SELECT `news_id` FROM `news_groups` LEFT JOIN `klimat_prod_cat` ON (`group_id`=`number`) WHERE `hidden`=0 AND `group_id` = '$category"."$sub'");
    }

    public function getCatNewsLH($low,$high)
    {
      return $this->db->query("SELECT `news_id` FROM `news_groups` LEFT JOIN `klimat_prod_cat` ON (`group_id`=`number`) WHERE `hidden`=0 AND `group_id` BETWEEN $low AND $high");
    }

    //Döpa om
    public function getNewsFromCat($catsql, $categories, $search)
    {
        if ($catsql == "1")
           $catsql = "`id` IN ($categories) AND";

        elseif ($catsql == "2")
            $catsql = "1=0 AND";

        elseif ($catsql == "3")
            $catsql = "";
      
        return $this->db->query("SELECT `id`, `title`, `text`, `text2`, `mainlink`, `mainlink_activated`, `date_publish`, `date_publish2`, `date_publish3`,
                                                                                                               `date_publish4`, `user_id`, `text_source`, `text_source_activated`, `img_source`, `img_file`, `img_description`,
                                                                                                                `img_url_activated`, `img_url`,
                                                                                                                        CASE WHEN `date_publish4`<>'0000-00-00 00:00:00' AND `date_publish4` <= NOW() THEN `date_publish4` ELSE
                                                                                                                                CASE WHEN `date_publish3`<>'0000-00-00 00:00:00' AND `date_publish3` <= NOW() THEN `date_publish3` ELSE
                                                                                                                                        CASE WHEN `date_publish2`<>'0000-00-00 00:00:00' AND `date_publish2` <= NOW() THEN `date_publish2` ELSE
                                                                                                                                                `date_publish`
                                                                                                                                        END
                                                                                                                                END
                                                                                                                        END AS `date_publishx`
                                                                                                                FROM `news` WHERE $catsql NOW() BETWEEN `date_publish` AND `date_end` AND `approved`='1' AND `deleted`='0' AND
                                                                                                                        (`title` LIKE '%$search%' OR `text` LIKE '%$search%' OR `text2` LIKE '%$search%' OR `img_description` LIKE '%$search%') ORDER BY `date_publish` DESC, `id` DESC;");

        
    }


    //Döpa om
    public function getNewsFromCat2($catsql, $categories, $search)
    {
        if ($catsql == "1")
           $catsql = "`id` IN ($categories) AND";

        elseif ($catsql == "2")
            $catsql = "1=0 AND";

        elseif ($catsql == "3")
            $catsql = "";

        return $this->db->query("SELECT `id`, `title`, `text`, `text2`, `mainlink`, `mainlink_activated`, `date_publish`, `date_publish2`, `date_publish3`, `date_publish4`,
                                                                                                                `user_id`, `text_source`, `text_source_activated`, `img_source`, `img_file`, `img_description`,
                                                                                                                `img_url_activated`, `img_url`,
                                                                                                                CASE WHEN `date_publish4`<>'0000-00-00 00:00:00' AND `date_publish4` <= NOW() THEN `date_publish4` ELSE
                                                                                                                        CASE WHEN `date_publish3`<>'0000-00-00 00:00:00' AND `date_publish3` <= NOW() THEN `date_publish3` ELSE
                                                                                                                                CASE WHEN `date_publish2`<>'0000-00-00 00:00:00' AND `date_publish2` <= NOW() THEN `date_publish2` ELSE
                                                                                                                                        `date_publish`
                                                                                                                                END
                                                                                                                        END
                                                                                                                END AS `date_publishx`
                                                                                                                FROM `news` WHERE $catsql NOW() BETWEEN `date_publish` AND `date_end` AND `approved`='1' AND `deleted`='0' AND
                                                                                                                                                        MATCH(`title`, `text`, `text2`,`img_description`) AGAINST ('$search*' IN BOOLEAN MODE)
                                                                                                                                                ORDER BY `date_publish` DESC, `id` DESC;");
    }


    //Döpa om
    public function getNewsFromCat3($catsql, $categories)
    {
        if ($catsql == "1")
           $catsql = "`id` IN ($categories) AND";

        elseif ($catsql == "2")
            $catsql = "1=0 AND";

        elseif ($catsql == "3")
            $catsql = "";

        return $this->db->query("SELECT n.`id`, n.`title`, n.`text`, n.`text2`, n.`mainlink`, n.`mainlink_activated`,
                                        n.`date_publish`, n.`date_publish2`, n.`date_publish3`, n.`date_publish4`, n.`user_id`,
                                        n.`text_source`, n.`text_source_activated`, n.`img_source`, n.`img_file`, n.`img_description`,
                                        n.`img_url_activated`, n.`img_url`,
                                        CASE WHEN n.`date_publish4`<>'0000-00-00 00:00:00' AND n.`date_publish4` <= NOW() THEN n.`date_publish4` ELSE
                                                                                        CASE WHEN n.`date_publish3`<>'0000-00-00 00:00:00' AND n.`date_publish3` <= NOW() THEN n.`date_publish3` ELSE
                                                                                                CASE WHEN n.`date_publish2`<>'0000-00-00 00:00:00' AND n.`date_publish2` <= NOW() THEN n.`date_publish2` ELSE
                                                                                                        n.`date_publish`
                                                                                                END
                                                                                        END
                                                                                END AS `date_publishx`
                                        FROM `news` AS n
                                        WHERE $catsql
                                        n.`approved`='1' AND n.`deleted`='0' AND
                                        (NOW() BETWEEN `date_publish` AND n.`date_end`)
                                        ORDER BY `date_publishx` DESC, n.`id` DESC LIMIT 61;");
    }

    public function getNewsGroup($gn)
    {
        return $this->db->query("SELECT * FROM news_groups AS ng LEFT JOIN klimat_prod_cat AS kpc ON (ng.group_id=kpc.number) WHERE hidden=0 AND ng.news_id=?","i", $gn);
    }

    //Get can and number from klimat_prod_cat where number % 100 = 0 and hidden = 0
    public function getCatNum()
    {
        return $this->db->query("SELECT `cat`, `number` FROM `klimat_prod_cat` WHERE `number` % 100 = 0 AND `hidden`='0' ORDER BY `cat` ASC")/* or die(mysql_error())*/;
    }

    public function getSubCats($category)
    {
        return $this->db->query("SELECT kpc.`cat`, kpc.`number`, ng.`news_id`, n.`id`, n.`date_publish` FROM `klimat_prod_cat` AS kpc
										LEFT JOIN `news_groups` AS ng ON (kpc.`number`=ng.`group_id`)
										LEFT JOIN `news` AS n ON (ng.`news_id`=n.`id`)
										WHERE `number` BETWEEN '$category"."01' AND '$category"."99'
										AND `approved`='1' AND `deleted`='0' AND NOW() BETWEEN `date_publish` AND `date_end`
										AND `date_publish` > (NOW() - INTERVAL 3 MONTH) AND kpc.`hidden`='0' ORDER BY kpc.`cat` ASC;");
    }

    public function getCatNews($category,$sub)
    {
      return  $this->db->query("SELECT `news_id` FROM `news_groups` WHERE `group_id` = '$category"."$sub'");
    }

    public function getCatNews2($low,$high)
    {
        return $this->db->query("SELECT `news_id` FROM `news_groups` WHERE `group_id` BETWEEN $low AND $high");
    }

    public function getGet($catsql)
    {
     return $this->db->query("SELECT SQL_CALC_FOUND_ROWS `id`, `title`, `text`, `text2`, `mainlink`, `mainlink_activated`, `date_publish`,
                `date_publish2`, `date_publish3`, `date_publish4`, `user_id`, `text_source`, `img_source`, `img_file`,
                `img_description` FROM `news` WHERE $catsql `approved`='1' AND `deleted`='0'
                AND NOW() BETWEEN `date_publish`	AND `date_end` ORDER BY `date_publish` DESC, `id` DESC");
    }
}
?>
